#use encoding 'utf-8';
use DBI;
#use CGI;
my $dbname=shift;
$dbistring ="dbi:ODBC:$dbname";
$maindir = shift;

my $dbh= DBI->connect ($dbistring,'','') || die "could not connect\n";

#
#
#
=head
my $query="SELECT UP40MustRoll0910.Panchayat_Code, PANCHAYATS.Panchayat_Name, UP40MustRoll0910.Work_Code, UPWork_detail.work_name, UP40MustRoll0910.Tot_Attend, UP40MustRoll0910.Wage_Per_day, UP40MustRoll0910.amt_paid_measure, UP40MustRoll0910.MSR_No
FROM ((UP40Musterrollmaster0910 INNER JOIN (UP40MustRoll0910 INNER JOIN UPWork_detail ON (UP40MustRoll0910.Block_code = UPWork_detail.block_code) AND (UP40MustRoll0910.Panchayat_Code = UPWork_detail.Panchayat_code) AND (UP40MustRoll0910.Work_Code = UPWork_detail.work_code)) ON (UP40Musterrollmaster0910.Block_code = UP40MustRoll0910.Block_code) AND (UP40Musterrollmaster0910.MSR_No = UP40MustRoll0910.MSR_No) AND (UP40Musterrollmaster0910.Work_Code = UP40MustRoll0910.Work_Code)) INNER JOIN PANCHAYATS ON UP40MustRoll0910.Panchayat_Code = PANCHAYATS.Panchayat_Code) INNER JOIN Exe_Agency ON UPWork_detail.Exe_Agency_code = Exe_Agency.Exe_Agency_code
ORDER BY UP40MustRoll0910.Panchayat_Code, UP40MustRoll0910.Work_Code, UP40MustRoll0910.Tot_Attend DESC , UP40MustRoll0910.MSR_No DESC";
****/
=cut
my $query1="select distinct Panchayats.district_code,Panchayats.block_code,Panchayats.panchayat_code,Districts.district_name,Blocks.block_name,Panchayats.panchayat_name from 
(Panchayats inner join Districts 
on districts.district_code=panchayats.district_code)
inner join blocks  
on blocks.block_code=panchayats.block_code";
$sth=$dbh->prepare($query1);
$sth->execute();
my $district=0;
my $block=0;
my $panchayat=0;
my $count=0;
my %panchayats; #hash of hashes{ district_code=>{block_code=>(panchayat1,panchayat2,....),...},....}
%districts;
%blocks;
%panchayatnames;
while (@ary=$sth->fetchrow_array ()) {
 $district=($ary[0]!=$district) ? $ary[0]:$district;
 $block=($ary[1]!=$block) ? $ary[1]:$block;
 $panchayat=($ary[2]!=$panchayat) ? $ary[2]:$panchayat;
 $panchayatnames{$panchayat}=$ary[5];
 $districts{$district}=$ary[3];
 $blocks{$block}=$ary[4];
 #print "$count : $district $block $panchayat \n";
 $count++;
  #if (exists $panchayats[$block]){
  push @{$panchayats{$district}{$block}},$panchayat;
  #} else {
  #$panchayats{$block}=["$panchayat"];
  #}
 
}
=head
print %panchayats;
use Data::Dumper;
print Dumper(%panchayats);
=cut
			
$count=1;
#$maindir="c:/nregaout";

		#print join(',',@workcodes);
		#exit;
		#break;
		my $query="SELECT    UPWork_detail.work_code,UPWork_detail.work_name,temp.msr, temp.labour, temp2.mat,temp.paid
FROM( (select sum(tot_attend) as labour,sum(amt_paid_measure) as paid,msr_no as msr,work_code  from UP40MustRoll0910 group by msr_no,work_code) temp INNER JOIN UPWork_detail on 
 temp.Work_Code = UPWork_detail.work_code  ) inner join (select sum(bill_amt) as mat,work_code  from UP40Material_purchased0910 group by work_code) temp2 on temp2.work_code=UPWork_detail.work_code
WHERE  UPWork_detail.panchayat_code=?";

#my $query2="UP40Material_purchased0910

#ORDER BY PANCHAYATS.Panchayat_Name, UPWork_detail.work_name, UP40MustRoll0910.Tot_Attend DESC , UP40MustRoll0910.MSR_No DESC";
#GROUP By PANCHAYATS.Panchayat_Name,UP40MustRoll0910.Panchayat_code,UP40MustRoll0910.MSR_No
		$sth=$dbh->prepare($query);
		
foreach $district(keys(%panchayats)) {
my $distdir = $maindir."/".$districts{$district};
mkdir $distdir;
  foreach $block(keys %{$panchayats{$district}}) {
   $blockdir= $distdir."/".$blocks{$block};
   if (! -d $blockdir) { mkdir $blockdir;mkdir "$blockdir/works";mkdir "$blockdir/works/0910";}
	foreach $panchayat(@{$panchayats{$district}{$block}}) {
		$panchayatdir= $blockdir."/".$panchayatnames{$panchayat};
		#if (! -d $panchayat) { mkdir $panchayatdir;}
		
	
		#my $q= new CGI;
		open $fh,"> $blockdir/works_0910_".$panchayatnames{$panchayat}.".html";
		binmode $fh ,":utf8";
		binmode STDOUT, ":utf8";
		#print $fh "Content-type:text/html;charset=utf-8;\n\n";
		#print $fh "Content-type: text/html;charset=utf-8";
 		#('content="text/html; charset=utf-8"');
		print $fh qq(<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Work details</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="robots" content="index, follow" />

<link rel="stylesheet" href="../../styles.css" type="text/css" />
 </head>);
		#print $fh qq(<head> <link rel=stylesheet href="../../../style.css"></head>);
		print $fh qq(<body style="margin: 0px; padding: 0px;");
		print $fh qq(<h3 align=center> Works done in financial year 2009-10 </h3>);
		print $fh qq(<h3 align=center> Name of Panchayat: $panchayatnames{$panchayat}</h3>);

		print $fh qq(<table  style="height: 100%;width:100%;margin-top:40;" align=center cellpadding="10" cellspacing="0" border="1" >\n); # manipulte to look like you want it
 
		print $fh "<thead>";
		print $fh "<th>Sl.No.</th><th>Name of Work</th><th>Muster Rolls</th><th>Total Attend</th><th>Labour</th><th>Material</th><th>Total</th>";
		print $fh "</thead>";
		$count=1;
		$sth->execute($panchayat);
		$tot_attend=0;
		$tot_paid=0;
		@mustrolls=();
		print $fh "<tr> \n";
		while (@arry=$sth->fetchrow_array ()) {
				
				if($#{$mustrolls{$arry[0]}{'msrs'}} %7 ==6) {push @{$mustrolls{$arry[0]}{'msrs'}},"<br>";}
				push @{$mustrolls{$arry[0]}{'msrs'}},$arry[2]."(".sprintf("%d",$arry[3]).")";
				$mustrolls{$arry[0]}{'workname'}= $arry[1];
				#$tot_attend += $arry[3];
				$mustrolls{$arry[0]}{'tot_paid'}   += $arry[5];
				$mustrolls{$arry[0]}{'mat'}=$arry[4];
				$mustrolls{$arry[0]}{'tot_attend'}   += $arry[3];
		}
		$tot_labour=$tot_material=0;
		foreach my $workcode(keys %mustrolls) {	
			print $fh "  <td width=5%>\n";
			print $fh "		$count\n";
			print $fh "	 </td> \n";
			
			print $fh "  <td width=15%>\n";
			print $fh "		$mustrolls{$workcode}{'workname'}<br><font size=-2>[$workcode]</font>\n";
			print $fh "	 </td> \n";
			
			print $fh "  <td width=60% style='word-wrap:word-break'>\n";
			print $fh join(',',@{$mustrolls{$workcode}{'msrs'}})." ";
			print $fh "	 </td> \n";
			print $fh "  <td>\n";
			print $fh "		$mustrolls{$workcode}{'tot_attend'}\n";
			print $fh "	 </td> \n";
		
			print $fh "  <td>\n";
			print $fh "		$mustrolls{$workcode}{'tot_paid'}\n";
			print $fh "	 </td> \n";
			print $fh "  <td width=10%>\n";
			print $fh "		$mustrolls{$workcode}{'mat'}\n";
			print $fh "	 </td> \n";
			print $fh "  <td width=10%>\n";
			print $fh "		",$mustrolls{$workcode}{'mat'} + $mustrolls{$workcode}{'tot_paid'},"\n";
			print $fh "	 </td> \n";
			$tot_labour+=$mustrolls{$workcode}{'tot_paid'};
			$tot_material+=$mustrolls{$workcode}{'mat'};
			
			print $fh "</tr> \n";
			$count++;
						#@mustrolls=();
		}
		print $fh "<tr><td colspan=7>Total Labour=$tot_labour Total Material =$tot_material </td></tr>";
		print $fh "</table>";
		print $fh "</body></html>";
		close $fh;
		reset %mustrolls;
		$count=1;
		#exit;

	}
  }
}
